SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMDOCU_CLIE_Q33]
/*-------------------------------------------------------*/
/*---Empresa               OFISIS S.A.                --*/
/*---Cliente               SANTILLANA                 --*/
/*---Sistema               Tesoreria                  --*/
/*---M«dulo                Empresa                    --*/
/*---Programa              Deuda Vencida y por Vencer --*/
/*---Script                SP_TMDOCU_CLIE_Q33.sql     --*/
/*---Nombre SP             SP_TMDOCU_CLIE_Q33         --*/
/*---Desarrollado por      Reynaldo Menacho Luyo      --*/
/*---Fecha Creaci«n        03/01/2001                 --*/
/*---Base Datos            ORACLE		      --*/
/*---Versi«n               8.0                        --*/
/*---Invoca a SP                                      --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Modificado 1 por	   REYNALDO MENACHO LUYO     	--*/
/*---Fecha Modificaci«n    05/09/2001			--*/ 
/*---Detalle Modificaci«n  Adaptacion para reporte de   --*/
/*---			   Estado de Cuenta por Cobrar  --*/
/*---			   (Original: Kardex)		--*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Drop Proc SP_TMDOCU_CLIE_Q33                      --*/
/*---Grant all on SP_TMDOCU_CLIE_Q33 to public       --*/ 
/*---SP_TMDOCU_CLIE_Q33 ' Where 1 = 1 ', '', 'S', '2003/03/24','SOL', 'N', 3.5, 'R','1'  --*/
/*-------------------------------------------------------*/
 @ISCA_WHER_0001 VARCHAR(255),
 @ISCA_WHER_0002 VARCHAR(255),
 @ISST_CONS TD_VC_001,
 @IDFE_REPO TD_DT_001,
 @ISCO_MONE_REPO TD_VC_003,
 @ISST_USUA TD_VC_001,
 @INFA_USUA numeric(10,6),
 @ISTI_REPO TD_VC_001,
 @ISDE_ORDE TD_VC_001

AS

 CREATE TABLE #TWDOCU_CLIE_Q33_1 
 ( CO_EMPR      varchar(3) NULL,
   CO_TIPO_DOCU	varchar(3)  NULL ,
   NU_DOCU_CLIE	varchar(20)  NULL,
   CO_CLIE	varchar(20) NULL ,
   FE_EMIS	datetime NULL,
   FE_VENC  	datetime NULL,
   CO_MONE	varchar(03) NULL,
   FA_TIPO_CAMB	numeric(9,4) NULL,
   IM_CXCB	numeric(16,4) NULL DEFAULT 0,
   IM_PAGO	numeric(16,4) NULL DEFAULT 0,
   DE_OBSE	varchar(100),
   CO_ESTA_DOCU varchar(3))

CREATE TABLE #TWDOCU_CLIE_Q33_2 
 ( CO_EMPR      varchar(3) NULL,
   CO_TIPO_DOCU	varchar(3)  NULL ,
   NU_DOCU_CLIE	varchar(20)  NULL,
   CO_CLIE	varchar(20) NULL ,
   FE_EMIS	datetime NULL,
   FE_VENC	datetime NULL,
   FA_TIPO_CAMB	numeric(9,4) NULL,
   IM_DEUD	numeric(16,4) NULL DEFAULT 0,
   IM_DEVN	numeric(16,4) NULL DEFAULT 0,
   IM_DEUD_CONV	numeric(16,4) NULL DEFAULT 0,
   IM_DEVN_CONV	numeric(16,4) NULL DEFAULT 0)

DECLARE 

@VSDE_LETR  	TD_VC_100,
@VSCO_EMPR	TD_VC_002,
@VSCO_CLIE  	TD_VC_020,
@VSNU_CANJ  	TD_VC_008,
@VSNU_RENO  	TD_VC_008,
@VSDE_ORDE	TD_VC_050,
@VSDE_ORDE_EMPR TD_VC_050,
@VSDE_SELE	VARCHAR(1000),
@VSDE_FROM	TD_VC_100,
@VSDE_WHER	VARCHAR(200),
@VSDE_GROU	TD_VC_100,
@VSTI_CAMB	TD_VC_001,
@VSTI_ORIG	TD_VC_001,
@VSFA_CAMB	VARCHAR(30),
@VNFE_0001	TD_IN_001,


@VSFE_CANC	TD_VC_020,

@CSCO_EMPR	TD_VC_002,
@CSCO_CLIE	TD_VC_020,
@CSCO_TIPO_DOCU	TD_VC_003,
@CSNU_DOCU_CLIE	TD_VC_020,
@CSCO_MONE	TD_VC_003,
@CNIM_CXCB	TD_NU_016_004,
@CNIM_PAGO	TD_NU_016_004,
@CDFE_EMIS	TD_DT_001,
@CNFA_TIPO_CAMB NUMERIC(10,6),

--@VSDE_CODI 	VARCHAR(500)
@OSTI_DOCU 	TD_VC_003,
@OSTI_DOCU_0001	TD_VC_003,
@OSTI_DOCU_0002	TD_VC_003

  SELECT @VSFE_CANC = CONVERT(VARCHAR(10), @IDFE_REPO, 103)

  IF @ISST_USUA = 'S'
    SELECT @VSFA_CAMB = CONVERT(VARCHAR(10), @INFA_USUA)
  ELSE
    SELECT @VSFA_CAMB = ' Max(T1.FA_TIPO_CAMB) '

  IF @ISTI_REPO = 'D'
    SELECT @VSFA_CAMB = ' T1.FA_TIPO_CAMB '

 IF @ISDE_ORDE = '1'
     IF @ISST_CONS = 'S'
	SELECT @VSDE_ORDE = ' Order By 1, 4, 3 '
     ELSE	
	SELECT @VSDE_ORDE = ' Order By 1, 3, 6, 5 '
 ELSE
     IF @ISST_CONS = 'S'
	SELECT @VSDE_ORDE = ' Order By 1, 3 '
     ELSE
	SELECT @VSDE_ORDE = ' Order By 1, 3, 5 '

  Execute (" INSERT INTO #TWDOCU_CLIE_Q33_1 
  (CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, FE_EMIS, FE_VENC, 
  CO_MONE, FA_TIPO_CAMB, IM_CXCB, IM_PAGO, DE_OBSE, CO_ESTA_DOCU)
  Select t1.CO_EMPR, t1.CO_CLIE, t1.CO_TIPO_DOCU, t1.NU_DOCU_CLIE, t1.FE_EMIS, t1.FE_VENC, 
	t1.CO_MONE, t1.FA_TIPO_CAMB, t1.IM_TOTA, t1.IM_PAGO, t1.DE_OBSE, t1.CO_ESTA_DOCU
  From ( Select TMDOCC_GENE.CO_EMPR, TMDOCC_GENE.CO_CLIE, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE, TMDOCC_GENE.FE_EMIS, TMDOCC_GENE.FE_VENC, 
		TMDOCC_GENE.CO_MONE, TMDOCC_GENE.FA_TIPO_CAMB, 
		(PATINDEX(TMDOCC_GENE.TI_DOCU_ANTC, 'N') - PATINDEX(TMDOCC_GENE.TI_DOCU_ANTC, 'B') - PATINDEX(TMDOCC_GENE.TI_DOCU_ANTC, 'C')) *
		(PATINDEX(t8.ST_SIGN, 'S') - PATINDEX(t8.ST_SIGN, 'N')) * TMDOCC_GENE.IM_TOTA IM_TOTA, 
	     	(PATINDEX(TMDOCC_GENE.TI_DOCU_ANTC, 'N') - PATINDEX(TMDOCC_GENE.TI_DOCU_ANTC, 'B') - PATINDEX(TMDOCC_GENE.TI_DOCU_ANTC, 'C')) *
		(PATINDEX(t8.ST_SIGN, 'S') - PATINDEX(t8.ST_SIGN, 'N')) * 
		    ((Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +
			  	    PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)
			From TCMOVI_BANC t11, TDMOVI_BANC t12, TMCNTA_BANC t13
			Where t11.FE_OPER <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
			And TMDOCC_GENE.TI_DOCU_ANTC = 'N'
			And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
			And TMDOCC_GENE.CO_TIPO_DOCU = t12.TI_DOCU_ENTI
			And TMDOCC_GENE.NU_DOCU_CLIE = t12.NU_DOCU_ENTI
			And t11.TI_SITU != 'ANU'
			And t11.CO_EMPR = t12.CO_EMPR
			And t11.CO_UNID_CONC = t12.CO_UNID_CONC
			And t11.CO_BANC = t12.CO_BANC
			And t11.NU_CNTA_BANC  = t12.NU_CNTA_BANC
			And t11.AA_BNCO = t12.AA_BNCO
			And t11.MM_BNCO = t12.MM_BNCO
			And t11.TI_MOVI_BANC  = t12.TI_MOVI_BANC
			And t11.NU_COMP_BANC = t12.NU_COMP_BANC
			And t11.CO_EMPR = t13.CO_EMPR
			And t11.CO_BANC = t13.CO_BANC
			And t11.NU_CNTA_BANC  = t13.NU_CNTA_BANC) +

		(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +
			  	PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)
		From TCMOVI_CAJA t11, TDMOVI_CAJA t12, TMCAJA t13
		Where t11.FE_OPER <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
		And TMDOCC_GENE.TI_DOCU_ANTC = 'N'
		And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
		And TMDOCC_GENE.CO_TIPO_DOCU = t12.TI_DOCU_ENTI
		And TMDOCC_GENE.NU_DOCU_CLIE = t12.NU_DOCU_ENTI
		And t11.TI_SITU != 'ANU'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_CAJA = t12.CO_CAJA
		And t11.AA_CAJA = t12.AA_CAJA
		And t11.MM_CAJA = t12.MM_CAJA
		And t11.TI_MOVI  = t12.TI_MOVI
		And t11.NU_COMP_CAJA = t12.NU_COMP_CAJA
		And t11.CO_EMPR = t13.CO_EMPR
		And t11.CO_CAJA = t13.CO_CAJA) +

		(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_COBR_CDES,0) +
			  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_CDES,0)),0)
		From TCAMAR_NCCL t11, TDAMAR_NCCL t12
		Where t11.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
		And TMDOCC_GENE.TI_DOCU_ANTC = 'N'
		And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
		And TMDOCC_GENE.CO_TIPO_DOCU = t12.CO_TIDO_DEST
		And TMDOCC_GENE.NU_DOCU_CLIE = t12.NU_DOCU_DEST
		And t11.TI_SITU != 'ANU'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_UNID_CONC = t12.CO_UNID_CONC
		And t11.NU_AMAR = t12.NU_AMAR
		And t11.FE_AMAR = t12.FE_AMAR) +

		(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_COBR_CORI,0) +
			  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_CORI,0)),0)
		From TCAMAR_NCCL t11, TDAMAR_NCCL t12
		Where t11.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
		And TMDOCC_GENE.TI_DOCU_ANTC = 'N'
		And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
		And TMDOCC_GENE.CO_TIPO_DOCU = t12.CO_TIDO_ORIG
		And TMDOCC_GENE.NU_DOCU_CLIE = t12.NU_DOCU_ORIG
		And t11.TI_SITU != 'ANU'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_UNID_CONC = t12.CO_UNID_CONC
		And t11.NU_AMAR = t12.NU_AMAR
		And t11.FE_AMAR = t12.FE_AMAR) +

		(Select ISNULL(SUM( PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CANJ_MDOC,0) +
			  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
		From TCCANJ_CLIE t11, TDCANJ_CLIE t12
		Where t11.FE_CANJ <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
		And TMDOCC_GENE.TI_DOCU_ANTC = 'N'
		And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
		And TMDOCC_GENE.CO_TIPO_DOCU = t12.CO_TIPO_DOCU
		And TMDOCC_GENE.NU_DOCU_CLIE = t12.NU_DOCU_CLIE
		And t11.TI_SITU != 'ANU'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_UNID_CONC = t12.CO_UNID_CONC
		And t11.CO_CLIE = t12.CO_CLIE
		And t11.NU_CANJ = t12.NU_CANJ
		And t11.FE_CANJ = t12.FE_CANJ) +

			   
		(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_RENO_MDOC,0) +
			  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
		From TCRENO_CLIE t11, TDRENO_CLIE t12
		Where t11.FE_RENO <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
		And TMDOCC_GENE.TI_DOCU_ANTC = 'N'
		And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
		And TMDOCC_GENE.CO_TIPO_DOCU = t12.CO_TIPO_DOCU
		And TMDOCC_GENE.NU_DOCU_CLIE = t12.NU_DOCU_CLIE
		And t11.TI_SITU != 'ANU'
		And t12.TI_SITU != 'ANU'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_UNID_CONC = t12.CO_UNID_CONC
		And t11.CO_CLIE = t12.CO_CLIE
		And t11.NU_RENO = t12.NU_RENO
		And t11.FE_RENO = t12.FE_RENO) +

		(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU, 0) +
			  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
		From TCAMAR_ANTC t11, TDAMAR_ANTC t12
		Where t12.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
		And t12.TI_ENTI = 'C'
		And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
		And TMDOCC_GENE.CO_TIPO_DOCU = t12.TI_DOCU_ENTI
		And TMDOCC_GENE.NU_DOCU_CLIE = t12.NU_DOCU_ENTI
		And t11.TI_SITU != 'ANU'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_UNID_CONC = t12.CO_UNID_CONC
		And t11.NU_AMAR = t12.NU_AMAR
		And t11.FE_AMAR = t12.FE_AMAR) +

		(Select ISNULL(SUM(ISNULL(t12.IM_DETA_NEGA,0)),0)
		From TCAMAR_ANTC t11, TDAMAR_ANTC t12
		Where t12.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
		And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
		And TMDOCC_GENE.CO_CLAV = t11.CO_BANC+t11.NU_CNTA_BANC+ t11.AA_BNCO + t11.MM_BNCO + t11.NU_COMP_BANC
		And t11.TI_SITU = 'ACT'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_UNID_CONC = t12.CO_UNID_CONC
		And t11.NU_AMAR = t12.NU_AMAR
		And t11.FE_AMAR = t12.FE_AMAR)  +

		(Select ISNULL(SUM(ISNULL(t12.IM_DETA_NEGA,0)),0)
		From TCAMAR_ANTC t11, TDAMAR_ANTC t12
		Where t12.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
		And TMDOCC_GENE.CO_EMPR = t12.CO_EMPR
		And TMDOCC_GENE.CO_CLAV = t11.CO_CAJA + t11.AA_CAJA + t11.MM_CAJA + t11.NU_COMP_CAJA
		And t11.TI_SITU = 'ACT'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_UNID_CONC = t12.CO_UNID_CONC
		And t11.NU_AMAR = t12.NU_AMAR
		And t11.FE_AMAR = t12.FE_AMAR))  IM_PAGO, TMDOCC_GENE.DE_OBSE, TMDOCC_GENE.CO_ESTA_DOCU
	From TMDOCC_GENE, TTDOCU_CNTB t8 " + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "
	And TMDOCC_GENE.FE_EMIS <= Convert(Datetime, '" + @VSFE_CANC + "', 103) 
	And TMDOCC_GENE.CO_ESTA_DOCU != 'ANU'
	And TMDOCC_GENE.CO_TIPO_DOCU = t8.TI_DOCU
	And t8.ST_DOCU_0017 = 'N') t1
  WHERE ABS(t1.IM_TOTA) > ABS(t1.IM_PAGO)")

------------------------------------------------------------------
-----	CONVIERTO LOS IMPORTES A LA MONEDA DE PRESENTACION
------------------------------------------------------------------

 IF @ISST_USUA = 'S'
	INSERT INTO #TWDOCU_CLIE_Q33_2(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, FE_EMIS, FE_VENC, FA_TIPO_CAMB, 
		IM_DEUD_CONV)
	SELECT T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.FE_EMIS, T1.FE_VENC, T1.FA_TIPO_CAMB, 
		OFITESO.dbo.FN_TCFACT_CAMB_Q02(T1.CO_MONE, @ISCO_MONE_REPO, T1.FE_EMIS, 
		T1.IM_CXCB - T1.IM_PAGO, T2.TI_CAMB, T2.TI_ORIG, 'S', T2.CO_MONE_DEFA, T2.CO_MONE_EXTR, @INFA_USUA)
	FROM #TWDOCU_CLIE_Q33_1 T1, TMPARA_TESO T2
	WHERE T1.CO_EMPR = T2.CO_EMPR
 ELSE
	INSERT INTO #TWDOCU_CLIE_Q33_2(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, FE_EMIS, FE_VENC, FA_TIPO_CAMB, 
		IM_DEUD_CONV)
	SELECT T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.FE_EMIS, T1.FE_VENC, T1.FA_TIPO_CAMB, 
		OFITESO.dbo.FN_TCFACT_CAMB_Q02(T1.CO_MONE, @ISCO_MONE_REPO, T1.FE_EMIS, 
		T1.IM_CXCB - T1.IM_PAGO, T2.TI_CAMB, T2.TI_ORIG, 'S', T2.CO_MONE_DEFA, T2.CO_MONE_EXTR, T1.FA_TIPO_CAMB)
	FROM #TWDOCU_CLIE_Q33_1 T1, TMPARA_TESO T2
	WHERE T1.CO_EMPR = T2.CO_EMPR


	UPDATE #TWDOCU_CLIE_Q33_2
	SET IM_DEVN_CONV = IM_DEUD_CONV,
	    IM_DEUD_CONV = 0
	WHERE FE_VENC <= @IDFE_REPO


 IF @ISST_CONS = 'S'
    BEGIN
	SELECT @VSDE_SELE = 'SELECT T2.TI_CLIE, T2.DE_TIPO_CLIE, T1.CO_CLIE, T2.NO_AUXI, 
		ROUND(SUM(T1.IM_DEUD_CONV)/1000,2),  ROUND(SUM(T1.IM_DEVN_CONV)/1000, 2),
		OFITESO.dbo.FN_TCFACT_CAMB_Q02(Max(T2.CO_MONE_CRED), "' + @ISCO_MONE_REPO + '",
		GETDATE(), Max(T2.IM_LIMI_CRED)/1000, Max(T3.TI_CAMB), Max(T3.TI_ORIG), "S", 
		Max(T3.CO_MONE_DEFA), Max(T3.CO_MONE_EXTR), '+@VSFA_CAMB+'), 0, MAX(T2.DE_OBSE) '
	SELECT @VSDE_FROM = 'FROM #TWDOCU_CLIE_Q33_2 T1, TMAUXI_EMPR T2, TMPARA_TESO T3 '
	SELECT @VSDE_WHER = 
		'WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_CLIE = T2.CO_AUXI_EMPR
		AND 	T2.TI_AUXI_EMPR = "C" 
		AND 	T1.CO_EMPR = T3.CO_EMPR '
	SELECT @VSDE_GROU = 'GROUP BY T2.TI_CLIE, T2.DE_TIPO_CLIE, T1.CO_CLIE, T2.NO_AUXI '
    END
 ELSE
    BEGIN
	SELECT @VSDE_SELE = 'SELECT T1.CO_EMPR, T4.DE_NOMB, T2.TI_CLIE, T2.DE_TIPO_CLIE, T1.CO_CLIE, T2.NO_AUXI, 
		ROUND(SUM(T1.IM_DEUD_CONV)/1000,2),  ROUND(SUM(T1.IM_DEVN_CONV)/1000, 2),
		OFITESO.dbo.FN_TCFACT_CAMB_Q02(Max(T2.CO_MONE_CRED), "' + @ISCO_MONE_REPO + '",
		GETDATE(), Max(T2.IM_LIMI_CRED)/1000, Max(T3.TI_CAMB), Max(T3.TI_ORIG), "S", 
		Max(T3.CO_MONE_DEFA), Max(T3.CO_MONE_EXTR), '+@VSFA_CAMB+'),
		OFITESO.dbo.FN_TCFACT_CAMB_Q02(Max(T2.CO_MONE_CRED), "' + @ISCO_MONE_REPO + '",
		GETDATE(), Max(T2.IM_CRED_EXED)/1000, Max(T3.TI_CAMB), Max(T3.TI_ORIG), "S", 
		Max(T3.CO_MONE_DEFA), Max(T3.CO_MONE_EXTR), '+@VSFA_CAMB+'), 0, MAX(T2.DE_OBSE) ' 
	SELECT @VSDE_FROM = 'FROM #TWDOCU_CLIE_Q33_2 T1, TMAUXI_EMPR T2, TMEMPR T4, TMPARA_TESO T3 '
 	SELECT @VSDE_WHER = 
		'WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_CLIE = T2.CO_AUXI_EMPR
		AND 	T2.TI_AUXI_EMPR = "C"
		AND 	T2.CO_EMPR = T4.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR '
	SELECT @VSDE_GROU = 'GROUP BY T1.CO_EMPR, T4.DE_NOMB, T2.TI_CLIE, T2.DE_TIPO_CLIE, T1.CO_CLIE, T2.NO_AUXI '
    END

	IF @ISTI_REPO = 'R'
 	    Execute ( @VSDE_SELE+@VSDE_FROM+@VSDE_WHER+@VSDE_GROU+@VSDE_ORDE)
 	ELSE
	    Execute ( 'SELECT T1.CO_EMPR, T4.DE_NOMB, T2.TI_CLIE, T2.DE_TIPO_CLIE, T1.CO_CLIE, 
		T2.NO_AUXI, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, ROUND(T1.IM_DEUD_CONV/1000,2),  
		ROUND(T1.IM_DEVN_CONV/1000, 2), 
		OFITESO.dbo.FN_TCFACT_CAMB_Q02(T2.CO_MONE_CRED, "' + @ISCO_MONE_REPO + '",
		GETDATE(), T2.IM_LIMI_CRED/1000, T3.TI_CAMB, T3.TI_ORIG, "S", 
		T3.CO_MONE_DEFA, T3.CO_MONE_EXTR, '+@VSFA_CAMB+'),
		OFITESO.dbo.FN_TCFACT_CAMB_Q02(T2.CO_MONE_CRED, "' + @ISCO_MONE_REPO + '",
		GETDATE(), T2.IM_CRED_EXED/1000, T3.TI_CAMB, T3.TI_ORIG, "S", 
		T3.CO_MONE_DEFA, T3.CO_MONE_EXTR, '+@VSFA_CAMB+'), 0, T2.DE_OBSE 
		'+@VSDE_FROM+@VSDE_WHER+'
		ORDER BY T1.CO_EMPR, T4.DE_NOMB, T2.TI_CLIE, T2.DE_TIPO_CLIE, T1.CO_CLIE, T2.NO_AUXI, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE')
RETURN
/*------------------------- FIN ------------------------------ */


GO
